package jsyf.oa.dao.report.attendance;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import jsyf.com.util.DateUtils;
import jsyf.oa.entity.system.SignInEntity;
import jsyf.oa.idao.BaseDao;
import jsyf.oa.ientity.IBaseEntity;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * Created by Administrator on 2018/4/7 0007.
 */
@Repository
public class AttendanceDao extends BaseDao {

    public AttendanceDao() {
        super.iBaseEntity = new SignInEntity();
    }

    @Override
    public IBaseEntity convertJson2Entity(JSONObject json) {
        return JSON.toJavaObject(json, SignInEntity.class);
    }

    public Integer getPageCount(String userId, String dateTime) {
        StringBuffer sql = new StringBuffer();
        sql.append(" select sum(tmpcount) from (\n" +
                "SELECT COUNT(1) as tmpcount FROM t_sys_sign_in\n" +
                "WHERE 1=1 AND USER_ID='" + userId + "'\n" +
                "AND SIGN_DATE='" + dateTime + "'\n" +
                "UNION ALL\n" +
                "SELECT COUNT(1) as tmpcount FROM t_sys_leave\n" +
                "WHERE 1=1 and USER_ID='" + userId + "'\n" +
                "AND LEAVE_DATE='" + dateTime + "') signLeave");
        String count = super.queryForString(sql.toString());
        return Integer.parseInt(count);
    }
    public Integer getLeaveCount(String userId, String dateTime) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT\n" +
                "\tCOUNT(1)\n" +
                "FROM\n" +
                "\tt_sys_leave\n" +
                "WHERE\n" +
                "\tUSER_ID = '"+userId+"'\n" +
                "AND LEAVE_DATE = '"+dateTime+"'");
        String count = super.queryForString(sql.toString());
        return Integer.parseInt(count);
    }

    public JSONObject getSignPersonByToday(String userId, String dateTime) {
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT\n" +
                "\t'0' AS signState,\n" +
                "\tSIGN_URL AS signUrl,\n" +
                "\tSIGN_OUT_URL AS signOutUrl,\n" +
                "\tt_sys_sign_in.LONGITUDE AS longitude,\n" +
                "\tt_sys_sign_in.LATITUDE AS latitude,\n" +
                "SUBSTRING(t_sys_sign_in.CREATE_TIME,12,5) AS signCreateTime,\n" +
                "t_sys_role_name.FRONT_FLAG AS frontFlag\n" +
                "\n" +
                "FROM\n" +
                "\tt_sys_sign_in\n" +
                "LEFT JOIN t_sys_user ON t_sys_sign_in.USER_ID=t_sys_user.ID\n" +
                "LEFT JOIN t_sys_role ON t_sys_user.ROLE_ID=t_sys_role.ID\n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID=t_sys_role_name.ID\n" +
                "WHERE\n" +
                "1=1\n" +
                "AND \n" +
                "USER_ID='" + userId + "'\n" +
                "AND SIGN_DATE='" + dateTime + "'");
        return super.queryForJsonObject(sb.toString());
    }

    public JSONObject getLeavePersonByToday(String userId, String dateTime) {
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT\n" +
                "\t'1' AS signState,\n" +
                "\tREMARK AS remark\n" +
                "FROM\n" +
                "\tt_sys_leave\n" +
                "WHERE\n" +
                "1=1\n" +
                "AND \n" +
                "USER_ID='" + userId + "'\n" +
                "AND LEAVE_DATE='" + dateTime + "'");
        return super.queryForJsonObject(sb.toString());
    }

    public List<JSONObject> getSignPersonByUserIdAndDateTime(String userId, String dateTime) {
        StringBuffer sb = new StringBuffer();
        sb.append(" select \n" +
                "id,\n" +
                "userId,\n" +
                "startAddress,\n" +
                "signUrl,\n" +
                "signDate,\n" +
                "SUBSTRING(signCreateTime,12,5) AS signCreateTime,\n" +
                "SUBSTRING(signOutTime,12,5) AS signOutTime,\n" +
                "endAddress,\n" +
                "signOutUrl,\n" +
                "signState\n" +
                " from v_sys_sign_leave where userId='" + userId + "' AND signDate='" + dateTime + "'");
        return super.queryForJsonList(sb.toString());
    }

    /**
     * 获取某人本月应出勤、截至今日应出勤、请假、缺勤汇总
     *
     * @return
     */
    public JSONObject getSignInByUserId(String userId, String dateTime, Integer frontFlag, String nowTime, String firstTime, String entryDate) {
        String entryTime = entryDate.substring(0, 7);
        String dateTimes = dateTime.substring(0, 7);
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT ");
        if (dateTimes.equals(entryTime)) {
            sb.append("IFNULL((SELECT COUNT(1) from t_sys_should_holiday WHERE SHOULD_DATE >= " + "'" + entryDate + "' AND LEFT(SHOULD_DATE,7)=" + "'" + entryTime + "' \n" +
                    "AND HOLIDAY_FLAG=0 AND FRONT_FLAG=" + frontFlag + "),0) AS shouldCount, ");
        } else {
            sb.append("IFNULL((SELECT SHOULD_SIGN_IN FROM t_sys_should_holiday WHERE LEFT (SHOULD_DATE, 7) = " + "'" + dateTimes + "'\tAND FRONT_FLAG = " + frontFlag + "\tGROUP BY\tSHOULD_SIGN_IN),0) AS shouldCount, ");
        }
        sb.append("(SELECT COUNT(*) FROM t_sys_sign_in WHERE LEFT (SIGN_DATE, 7) = " + "'" + dateTimes + "'  AND USER_ID = " + "'" + userId + "') AS signInCount,");
        if (dateTime.equals(nowTime)) {
            sb.append("(SELECT COUNT(*) FROM t_sys_leave WHERE LEAVE_DATE BETWEEN " + "'" + firstTime + "' AND " + "'" + nowTime + "'  AND USER_ID = " + "'" + userId + "') AS leaveCount,");
        } else {
            sb.append("(SELECT COUNT(*) FROM t_sys_leave WHERE LEFT(LEAVE_DATE,7)=" + "'" + dateTimes + "'   AND USER_ID = " + "'" + userId + "') AS leaveCount,");
        }
        sb.append("  (SELECT COUNT(1) FROM t_sys_should_holiday WHERE FRONT_FLAG = " + frontFlag + " AND HOLIDAY_FLAG = 0 AND SHOULD_DATE like " + "'" + dateTimes + "%'" + "  AND SHOULD_DATE <= " + "'" + nowTime + "') AS todayCount");
        return super.queryForJsonObject(sb.toString());
    }


    public List<JSONObject> getRegionBySignInList(String userId, String dateTime, String departId, String frontFlag) {
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT");
        sb.append(" sysUser.*,\n");
        sb.append(" sysSign.signCreateTime,\n");
        sb.append(" sysSign.startAddress,\n");
        sb.append(" sysSign.signUrl,\n");
        sb.append(" sysSign.signOutTime,\n");
        sb.append(" sysSign.endAddress,\n");
        sb.append(" sysSign.signOutUrl,\n");
        sb.append(" sysSign.signState\n");
        sb.append(" FROM \n");
        sb.append(" ( SELECT \n");
        sb.append(" userId,departName,roleName,userName,CASE WHEN frontFlag=1\n" +
                "THEN\n" +
                "regionName\n" +
                "END AS regionName,'" + dateTime + "' as signDate\n");
        sb.append(" FROM v_sys_user_for_report\n");
        sb.append(" WHERE 1 = 1\n");
        sb.append(" AND (jobState <> 1 OR (jobState = 1 AND leaveDate >'" + dateTime + "'))\n");
        sb.append(" AND reportFlag=0 AND entryDate<='" + dateTime + "'\n");
        if (StringUtils.isNotBlank(userId)) {
            sb.append(" AND (userId ='" + userId + "' OR parentId ='" + userId + "'\n");
            if ("0".equals(frontFlag)) {
                sb.append(" OR (userId in(SELECT USER_ID FROM t_sys_user_2_region WHERE REGION_ID in(\n");
                sb.append(" SELECT REGION_ID FROM t_sys_user_2_region WHERE USER_ID = '" + userId + "')) AND v_sys_user_for_report.frontFlag = 1)\n");
            }
            sb.append(" OR (departId in (\n" +
                    "\t\t\t\t\t\t\t\tselect t_sys_depart.ID from t_sys_depart \n" +
                    "\t\t\t\t\t\t\t\t\tLEFT JOIN t_sys_role ON t_sys_depart.ID=t_sys_role.DEPART_ID\n" +
                    "\t\t\t\t\t\t\t\t\tLEFT JOIN t_sys_user ON t_sys_role.ID =t_sys_user.ROLE_ID\n" +
                    "\t\t\t\t\t\t\t\t\tWHERE t_sys_user.ID='" + userId + "')))\n");
        }
        if (StringUtils.isNotBlank(departId)) {
            sb.append(" AND departId='" + departId + "'");
        }
        sb.append(" GROUP BY userId,departName,roleName,userName,regionName\n");
        sb.append(" ORDER BY companySortNo,departSortNo,roleSortNo,userSortNo) as sysUser\n");
        sb.append("  LEFT JOIN (\n");
        sb.append(" select userId,signDate,SUBSTRING(signCreateTime,11,6) as signCreateTime,startAddress,SUBSTRING(signOutTime,11,6 ) as signOutTime,endAddress,signUrl,signOutUrl,signState\n");
        sb.append(" FROM\n");
        sb.append(" v_sys_sign_leave\n");
        sb.append(" GROUP BY userId,LEFT(signDate," + dateTime.length() + ")\n");
        sb.append(" ) as sysSign ON sysUser.userId = sysSign.userId and sysUser.signDate = sysSign.signDate");
        List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
        return jsonList;

    }

    public List<JSONObject> getRegionByMonthList(String userId, String dateTime, String departId, String nowTime,
                                                 String firstDay, String dayTime, String monthTime, String frontFlag) {
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT \n");
        sb.append(" sysUser.*, \n");
        if (dateTime.equals(monthTime)) {
            sb.append(" CASE \n" +
                    "WHEN\n" +
                    "" + dayTime + "-signInCount-leaveCount-shouldFull.shouldNum<0||jobState=1||entryDate like '" + dateTime + "%' \n" +
                    "THEN\n" +
                    "0\n" +
                    "ELSE\n" +
                    "" + dayTime + "-signInCount-leaveCount\n" +
                    "END  AS absenceCount\n");
        } else {
            sb.append("   CASE \n" +
                    "WHEN\n" +
                    "shouldCount-signInCount-leaveCount-shouldFull.shouldNum<0||jobState=1||entryDate like '" + dateTime + "%' \n" +
                    "THEN\n" +
                    "0\n" +
                    "ELSE\n" +
                    "shouldCount-signInCount-leaveCount\n" +
                    "END  AS absenceCount\n");
        }
        sb.append(" FROM\n");
        sb.append(" ( SELECT \n");
        sb.append(" userId,departName,roleName,userName,frontFlag,jobState,entryDate,CASE WHEN frontFlag=1\n" +
                "THEN\n" +
                "regionName\n" +
                "END AS regionName,\n");
        sb.append(" IFNULL((SELECT SHOULD_SIGN_IN FROM t_sys_should_holiday WHERE LEFT (SHOULD_DATE, 7) =" + "'" + dateTime + "'  AND FRONT_FLAG =v_sys_user_for_report.frontFlag GROUP BY\tSHOULD_SIGN_IN),0) AS shouldCount,\n");
        sb.append(" (SELECT COUNT(*) FROM t_sys_sign_in WHERE LEFT (SIGN_DATE, 7) =" + "'" + dateTime + "' AND USER_ID =`v_sys_user_for_report`.userId ) AS signInCount,\n");
        if (dateTime.equals(monthTime)) {
            sb.append(" (SELECT COUNT(*) FROM t_sys_leave WHERE LEAVE_DATE BETWEEN " + "'" + firstDay + "' AND " + "'" + nowTime + "' AND USER_ID =`v_sys_user_for_report`.userId ) AS leaveCount\n");

        } else {
            sb.append(" (SELECT COUNT(*) FROM t_sys_leave WHERE LEFT (LEAVE_DATE, 7) =" + "'" + dateTime + "'  AND USER_ID =`v_sys_user_for_report`.userId ) AS leaveCount\n");
        }
        sb.append(" FROM \n");
        sb.append(" v_sys_user_for_report\n");
        sb.append(" WHERE 1 = 1\n");
        sb.append(" AND (jobState <> 1 OR (jobState = 1 AND leaveDate >='" + dateTime + "'))\n");
        sb.append(" AND reportFlag=0 AND (entryDate<='" + dateTime + "' OR entryDate LIKE '" + dateTime + "%')\n");
        if (StringUtils.isNotBlank(userId)) {
            sb.append(" AND (userId ='" + userId + "' OR parentId ='" + userId + "'\n");
            if ("0".equals(frontFlag)) {
                sb.append(" OR (userId in(SELECT USER_ID FROM t_sys_user_2_region WHERE REGION_ID in(\n");
                sb.append(" SELECT REGION_ID FROM t_sys_user_2_region WHERE USER_ID = '" + userId + "')) AND v_sys_user_for_report.frontFlag = 1)\n");
            }
            sb.append(" OR (departId in (\n" +
                    "\t\t\t\t\t\t\t\tselect t_sys_depart.ID from t_sys_depart \n" +
                    "\t\t\t\t\t\t\t\t\tLEFT JOIN t_sys_role ON t_sys_depart.ID=t_sys_role.DEPART_ID\n" +
                    "\t\t\t\t\t\t\t\t\tLEFT JOIN t_sys_user ON t_sys_role.ID =t_sys_user.ROLE_ID\n" +
                    "\t\t\t\t\t\t\t\t\tWHERE t_sys_user.ID='" + userId + "')))\n");
        }
        if (StringUtils.isNotBlank(departId)) {
            sb.append(" AND departId='" + departId + "'");
        }
        sb.append(" GROUP BY userId,departName,roleName,userName,regionName\n");
        sb.append(" ORDER BY companySortNo,departSortNo,roleSortNo,userSortNo) as sysUser\n");
        sb.append("  LEFT JOIN (\n" +
                "SELECT\n" +
                "\tFRONT_FLAG,\n" +
                "\tCOUNT(1) shouldNum\n" +
                "FROM\n" +
                "\tt_sys_should_holiday\n" +
                "WHERE\n" +
                "\tSHOULD_DATE LIKE '" + dateTime + "%'\n" +
                "AND SHOULD_DATE <= '" + nowTime + "'\n" +
                "AND HOLIDAY_FLAG = 1\n" +
                "GROUP BY FRONT_FLAG\n" +
                ")AS shouldFull ON sysUser.frontFlag=shouldFull.FRONT_FLAG\n");
        List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
        return jsonList;
    }

    public List<JSONObject> getCompanyByTodayList(String userId, String dateTime, String companyId) {
        StringBuffer sb = new StringBuffer();
        int forWeek = 0;
        try {
            forWeek = DateUtils.dayForWeek(dateTime);
        } catch (Exception e) {
            e.printStackTrace();
        }
        sb.append(" SELECT\n" +
                "sysUser.*,\n" +
                "IFNULL(sysSign.signInCount,0) signInCount,\n" +
                "IFNULL(sysSign.leaveCount,0) leaveCount,\n");
        if (forWeek == 7 || forWeek == 6) {
            sb.append("0 AS absenceCount\n");
        } else {
            sb.append("IFNULL((sysUser.shouldCount-IFNULL(signInCount,0)-IFNULL(leaveCount,0)),0) AS absenceCount\n");
        }
        sb.append(
                "FROM\n" +
                        "(\n" +
                        "SELECT \n" +
                        "v_sys_user_for_report.userId,\n" +
                        "v_sys_user_for_report.departId,\n" +
                        "v_sys_user_for_report.departName,\n" +
                        "COUNT(1) AS shouldCount,\n" +
                        "'" + dateTime + "' AS signDate\n" +
                        " FROM v_sys_user_for_report\n");
        if (StringUtils.isNotBlank(userId)) {
            sb.append("WHERE v_sys_user_for_report.companyId in (\n" +
                    "\tSELECT t_sys_depart.COMPANY_ID FROM t_sys_depart WHERE ID in(\n" +
                    "\t\t\tSELECT t_sys_role.DEPART_ID from t_sys_role WHERE ID in(\n" +
                    "\t\t\t\tSELECT t_sys_user.ROLE_ID from t_sys_user WHERE ID = '" + userId + "'\n" +
                    "\t\t\t) OR ID in(SELECT t_sys_user_2_role.ROLE_ID FROM t_sys_user_2_role WHERE USER_ID='" + userId + "')\n" +
                    "\t\t)\n" +
                    ")\n");
        }
        if (StringUtils.isNotBlank(companyId)) {
            sb.append("WHERE v_sys_user_for_report.companyId='" + companyId + "' \n");
        }
        sb.append(
                "AND (jobState <> 1 OR (jobState = 1 AND leaveDate >'" + dateTime + "'))\n" +
                        "AND reportFlag=0 AND entryDate <='" + dateTime + "'\n\n" +
                        "GROUP BY departId,departName\n" +
                        "ORDER BY departSortNo)AS sysUser\n" +
                        "LEFT JOIN\n" +
                        "(SELECT departId,signDate,\n" +
                        "COUNT(case when signState=0 then 1 end)AS signInCount,\n" +
                        "COUNT(case when signState=1 then 1 end)AS leaveCount\n" +
                        " from v_sys_sign_leave WHERE reportFlag=0  GROUP BY departId,signDate) AS sysSign ON sysUser.departId=sysSign.departId AND sysUser.signDate=sysSign.signDate");
        List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
        return jsonList;

    }

    public List<JSONObject> getCompanyByMonthList(String userId, String dateTime, String companyId, String nowTime, String firstDay, String dayTime, String monthTime) {
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT \n");
        sb.append(" sysUser.*, \n");
        if (dateTime.equals(monthTime)) {
            sb.append(" CASE \n" +
                    "WHEN\n" +
                    "" + dayTime + "-signInCount-leaveCount-shouldFull.shouldNum<0||jobState=1||entryDate like '" + dateTime + "%' \n" +
                    "THEN\n" +
                    "0\n" +
                    "ELSE\n" +
                    "" + dayTime + "-signInCount-leaveCount-shouldFull.shouldNum\n" +
                    "END  AS absenceCount\n");
        } else {
            sb.append(" CASE \n" +
                    "WHEN\n" +
                    "shouldCount-signInCount-leaveCount<0||jobState=1||entryDate like '" + dateTime + "%'\n" +
                    "THEN\n" +
                    "0\n" +
                    "ELSE\n" +
                    "shouldCount-signInCount-leaveCount\n" +
                    "END  AS absenceCount\n");

        }
        sb.append(" FROM\n");
        sb.append(" ( SELECT \n");
        sb.append(" userId,departName,roleName,userName,frontFlag,jobState,entryDate," +
                "CASE WHEN frontFlag=1\n" +
                "THEN\n" +
                "regionName\n" +
                "END AS regionName,\n");
        sb.append(" IFNULL((SELECT SHOULD_SIGN_IN FROM t_sys_should_holiday WHERE LEFT (SHOULD_DATE, 7) =" + "'" + dateTime + "'  AND FRONT_FLAG =v_sys_user_for_report.frontFlag GROUP BY\tSHOULD_SIGN_IN),0) AS shouldCount,\n");
        sb.append(" (SELECT COUNT(*) FROM t_sys_sign_in WHERE LEFT (SIGN_DATE, 7) =" + "'" + dateTime + "' AND USER_ID =`v_sys_user_for_report`.userId ) AS signInCount,\n");
        if (dateTime.equals(monthTime)) {
            sb.append(" (SELECT COUNT(*) FROM t_sys_leave WHERE LEAVE_DATE BETWEEN " + "'" + firstDay + "' AND " + "'" + nowTime + "' AND USER_ID =`v_sys_user_for_report`.userId ) AS leaveCount\n");

        } else {
            sb.append(" (SELECT COUNT(*) FROM t_sys_leave WHERE LEFT (LEAVE_DATE, 7) =" + "'" + dateTime + "'  AND USER_ID =`v_sys_user_for_report`.userId ) AS leaveCount\n");
        }
        sb.append(" FROM \n");
        sb.append(" v_sys_user_for_report\n");
        sb.append(" WHERE 1 = 1\n");
        sb.append(" AND (jobState <> 1 OR (jobState = 1 AND leaveDate >='" + dateTime + "'))\n");
        if (StringUtils.isNotBlank(userId)) {
            sb.append(" AND v_sys_user_for_report.companyId in (\n" +
                    "\tSELECT t_sys_depart.COMPANY_ID FROM t_sys_depart WHERE ID in(\n" +
                    "\t\t\tSELECT t_sys_role.DEPART_ID from t_sys_role WHERE ID in(\n" +
                    "\t\t\t\tSELECT t_sys_user.ROLE_ID from t_sys_user WHERE ID = '" + userId + "'\n" +
                    "\t\t\t) OR ID in(SELECT t_sys_user_2_role.ROLE_ID FROM t_sys_user_2_role WHERE USER_ID='" + userId + "')\n" +
                    "\t\t)\n" +
                    ")\n");
        }
        if (StringUtils.isNotBlank(companyId)) {
            sb.append(" and v_sys_user_for_report.companyId='" + companyId + "' ");
        }
        sb.append("AND reportFlag=0 AND (entryDate<='" + dateTime + "' OR entryDate LIKE '" + dateTime + "%')\n");
        sb.append(" GROUP BY userId,departName,roleName,userName,regionName\n");
        sb.append(" ORDER BY companySortNo,departSortNo,roleSortNo,userSortNo) as sysUser\n");
        sb.append(" LEFT JOIN (\n" +
                "SELECT\n" +
                "\tFRONT_FLAG,\n" +
                "\tCOUNT(1) shouldNum\n" +
                "FROM\n" +
                "\tt_sys_should_holiday\n" +
                "WHERE\n" +
                "\tSHOULD_DATE LIKE '" + dateTime + "%'\n" +
                "AND SHOULD_DATE <= '" + nowTime + "'\n" +
                "AND HOLIDAY_FLAG = 1\n" +
                "GROUP BY FRONT_FLAG\n" +
                ")AS shouldFull ON sysUser.frontFlag=shouldFull.FRONT_FLAG\n");
        List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
        return jsonList;
    }

    public List<JSONObject> getGroupByTodayList(String dateTime, String sbCompanyId) {
        StringBuffer sb = new StringBuffer();
        int forWeek = 0;
        try {
            forWeek = DateUtils.dayForWeek(dateTime);
        } catch (Exception e) {
            e.printStackTrace();
        }
        sb.append("SELECT company.companyId,\n" +
                "company.companyName,\n" +
                "should.shouldSignInCount,\n" +
                "IFNULL(sysSign.signInFullCount,0)AS signInFullCount,\n" +
                "IFNULL(sysSign.leaveFullCount,0)AS leaveFullCount,\n");
        if (forWeek == 7 || forWeek == 6) {
            sb.append("0 AS absenceFullCount\n");
        } else {
            sb.append("should.shouldSignInCount-IFNULL(sysSign.signInFullCount,0)-IFNULL(sysSign.leaveFullCount,0) AS absenceFullCount\n");
        }

        sb.append("\n" +
                "FROM \n" +
                "(\n" +
                "\tSELECT ID as companyId,`NAME` as companyName,\n" +
                " '" + dateTime + "' AS signDate,\n" +
                "\tCASE \n" +
                "\t\tWHEN PARENT_ID = '0' THEN SORT_NO * 100 \n" +
                "\t\tELSE (SELECT (companyParent.SORT_NO*100 + t_sys_company.SORT_NO + 10) FROM t_sys_company as companyParent WHERE t_sys_company.PARENT_ID = companyParent.ID)\n" +
                "\tEND as sortNo\n" +
                "\t FROM t_sys_company WHERE 1=1  " + sbCompanyId + "\n" +
                ") as company\n" +
                "LEFT JOIN \n" +
                "(\n" +
                "\tSELECT t_sys_depart.COMPANY_ID as companyId,COUNT(1) as shouldSignInCount FROM t_sys_user\n" +
                "\tLEFT JOIN t_sys_role ON t_sys_user.ROLE_ID = t_sys_role.ID\n" +
                "\tLEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID=t_sys_role_name.ID \n" +
                "\tLEFT JOIN t_sys_depart ON t_sys_role.DEPART_ID = t_sys_depart.ID\n" +
                "\tLEFT JOIN t_sys_company ON t_sys_depart.COMPANY_ID = t_sys_company.ID\n" +
                "\tLEFT JOIN t_sys_user_leave ON t_sys_user.ID = t_sys_user_leave.USER_ID\n" +
                "\tWHERE t_sys_depart.DEL_FLAG = 0 AND t_sys_role_name.REPORT_FLAG=0 " +
                "AND t_sys_company.DEL_FLAG=0 AND t_sys_role.DEL_FLAG = 0 AND t_sys_user.ENTRY_DATE <='" + dateTime + "'" +
                "AND (t_sys_user.JOB_STATE <>  1 OR (t_sys_user.JOB_STATE = 1 && t_sys_user_leave.LEAVE_DATE > '" + dateTime + "'))\n" +
                "\tGROUP BY t_sys_depart.COMPANY_ID\n" +
                ") as should ON company.companyId = should.companyId\n" +
                "LEFT JOIN\n" +
                "(SELECT companyId,signDate,\n" +
                "COUNT(case when signState=0 then 1 end)AS signInFullCount,\n" +
                "COUNT(case when signState=1 then 1 end)AS leaveFullCount\n" +
                " from v_sys_sign_leave where 1=1 and reportFlag=0 GROUP BY companyId,signDate) AS sysSign ON company.companyId=sysSign.companyId AND company.signDate=sysSign.signDate\n" +
                "ORDER BY company.sortNo\n");
        List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
        return jsonList;

    }

    public List<JSONObject> getGroupByMonthList(String dateTime, String sbCompanyId, String nowTime) {
        StringBuffer sb = new StringBuffer();
        sb.append(
                "SELECT company.companyId,\n" +
                        "company.companyName,\n" +
                        "should.shouldSignInCount,\n" +
                        "IFNULL(singInFull.signInFullCount,0)AS signInFullCount,\n" +
                        "IFNULL(leaveInFull.leaveFullCount,0)AS leaveFullCount,\n" +
                        "0 AS absenceFullCount\n" +
                        "FROM \n" +
                        "(\n" +
                        "\tSELECT ID as companyId,`NAME` as companyName,\n" +
                        "\tCASE \n" +
                        "\t\tWHEN PARENT_ID = '0' THEN SORT_NO * 100 \n" +
                        "\t\tELSE (SELECT (companyParent.SORT_NO*100 + t_sys_company.SORT_NO + 10) FROM t_sys_company as companyParent WHERE t_sys_company.PARENT_ID = companyParent.ID)\n" +
                        "\tEND as sortNo\n" +
                        "\t FROM t_sys_company WHERE 1=1 AND DEL_FLAG = 0 \n");
        sb.append(sbCompanyId);
        sb.append("AND DEL_FLAG = 0 \n" +
                ") as company\n" +
                "LEFT JOIN \n" +
                "(\n" +
                "\tSELECT t_sys_depart.COMPANY_ID as companyId,COUNT(1) as shouldSignInCount FROM t_sys_user\n" +
                "\tLEFT JOIN t_sys_role ON t_sys_user.ROLE_ID = t_sys_role.ID\n" +
                "\tLEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID=t_sys_role_name.ID\n" +
                "\tLEFT JOIN t_sys_depart ON t_sys_role.DEPART_ID = t_sys_depart.ID\n" +
                "\tLEFT JOIN t_sys_company ON t_sys_depart.COMPANY_ID = t_sys_company.ID\n" +
                "\tLEFT JOIN t_sys_user_leave ON t_sys_user.ID = t_sys_user_leave.USER_ID\n" +
                "\tWHERE t_sys_depart.DEL_FLAG = 0  AND t_sys_role_name.REPORT_FLAG=0 " +
                "AND t_sys_company.DEL_FLAG=0 AND t_sys_role.DEL_FLAG = 0 AND (t_sys_user.ENTRY_DATE <='" + dateTime + "' OR t_sys_user.ENTRY_DATE LIKE '" + dateTime + "%')" +
                " AND (t_sys_user.JOB_STATE <>  1 OR (t_sys_user.JOB_STATE = 1 && t_sys_user_leave.LEAVE_DATE >= '" + dateTime + "'))\n" +
                "\tGROUP BY t_sys_depart.COMPANY_ID\n" +
                ") as should ON company.companyId = should.companyId\n" +
                "LEFT JOIN\n" +
                "(\n" +
                "\tSELECT t_sys_depart.COMPANY_ID as companyId,COUNT(1) as signInFullCount FROM \n" +
                "\t(\n" +
                "\t\tSELECT t_sys_sign_in.USER_ID as userId,COUNT(1) as signInCount FROM t_sys_sign_in \n" +
                "\t\tWHERE t_sys_sign_in.SIGN_DATE LIKE '" + dateTime + "%'  AND SIGN_DATE <= '" + nowTime + "'\n" +
                "\t\tGROUP BY t_sys_sign_in.USER_ID\n" +
                "\t) as sign\n" +
                "\tLEFT JOIN t_sys_user ON sign.userId = t_sys_user.ID\n" +
                "\tLEFT JOIN t_sys_role ON t_sys_user.ROLE_ID = t_sys_role.ID\n" +
                "\tLEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID = t_sys_role_name.ID\n" +
                "\tLEFT JOIN t_sys_depart ON t_sys_role.DEPART_ID = t_sys_depart.ID\n" +
                "\tLEFT JOIN(\n" +
                "\tSELECT FRONT_FLAG as frontFlag,COUNT(1) as shouldSignCount\n" +
                "\tFROM t_sys_should_holiday WHERE SHOULD_DATE LIKE '" + dateTime + "%' AND SHOULD_DATE <= '" + nowTime + "' AND HOLIDAY_FLAG = 0\n" +
                "\tGROUP BY FRONT_FLAG\n" +
                "\t) as shouldSign ON t_sys_role_name.FRONT_FLAG = shouldSign.frontFlag\n" +
                "\tWHERE sign.signInCount >= shouldSign.shouldSignCount\n" +
                "\tGROUP BY t_sys_depart.COMPANY_ID\n" +
                ") singInFull ON company.companyId = singInFull.companyId\n" +
                " LEFT JOIN\n" +
                "(\n" +
                "\tSELECT t_sys_depart.COMPANY_ID as companyId,COUNT(1) as leaveFullCount FROM \n" +
                "\t(\n" +
                "\t\tSELECT t_sys_leave.USER_ID as userId,COUNT(1) as leaveCount FROM t_sys_leave \n" +
                "\t\tWHERE t_sys_leave.LEAVE_DATE LIKE '" + dateTime + "%'  AND  t_sys_leave.LEAVE_DATE <= '" + nowTime + "'\n" +
                "\t\tGROUP BY t_sys_leave.USER_ID\n" +
                "\t) as leaveIn\n" +
                "\tLEFT JOIN t_sys_user ON leaveIn.userId = t_sys_user.ID\n" +
                "\tLEFT JOIN t_sys_role ON t_sys_user.ROLE_ID = t_sys_role.ID\n" +
                "\tLEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID = t_sys_role_name.ID\n" +
                "\tLEFT JOIN t_sys_depart ON t_sys_role.DEPART_ID = t_sys_depart.ID\n" +
                "GROUP BY t_sys_depart.COMPANY_ID\n" +
                ") leaveInFull ON company.companyId = leaveInFull.companyId");
        List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
        return jsonList;

    }

    public List<JSONObject> getForSignInByTypeAndDateTime(String dateTime, String companyId, String departId, String signState) {
        StringBuffer sb = new StringBuffer();
        sb.append("  SELECT sysUser.*,\n" +
                "sysSign.signCreateTime,\n" +
                " sysSign.startAddress,\n" +
                " sysSign.signUrl,\n" +
                " sysSign.signOutTime,\n" +
                " sysSign.endAddress,\n" +
                " sysSign.signOutUrl,\n" +
                " sysSign.signState\n" +
                " FROM \n" +
                " (\n" +
                "SELECT userId,companyId,companyName,roleName,userName,CASE WHEN frontFlag=1\n" +
                "THEN\n" +
                "regionName\n" +
                "END AS regionName,'" + dateTime + "' as signDate\n" +
                "FROM v_sys_user_for_report\n" +
                "WHERE 1 = 1\n");
        sb.append("AND entryDate <='" + dateTime + "'\n\n");
        sb.append("AND (jobState <> 1 OR (jobState = 1 AND leaveDate >'" + dateTime + "'))\n");
        if (StringUtils.isNotBlank(companyId)) {
            sb.append("AND companyId='" + companyId + "'\n");

        } else {
            sb.append("AND departId='" + departId + "'\n");
        }
        sb.append("AND reportFlag=0\n" +
                "GROUP BY userId,companyName,departName,roleName,userName,regionName \n" +
                "ORDER BY companySortNo,departSortNo,roleSortNo,userSortNo) as sysUser\n" +
                "LEFT JOIN\n" +
                "(\n" +
                "select userId,signDate,SUBSTRING(signCreateTime,11,6) as signCreateTime," +
                "startAddress,SUBSTRING(signOutTime,11,6 ) as signOutTime,endAddress,signUrl,signOutUrl,signState\n" +
                " FROM\n" +
                " v_sys_sign_leave\n" +
                "\n");
        sb.append(" GROUP BY userId,LEFT(signDate,10)\n");
        sb.append(
                " ) as sysSign ON sysUser.userId = sysSign.userId ");
        sb.append("and sysUser.signDate = left(sysSign.signDate,10)\n");
        sb.append(
                "WHERE\n" +
                        "1=1\n");
        if (StringUtils.isNotBlank(signState)) {
            sb.append("AND sysSign.signState ='" + signState + "'");
        } else {
            sb.append("AND sysSign.signState is null");
        }
        List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
        return jsonList;

    }

    public List<JSONObject> getForSignInByTypeAndMonthTime(String dateTime, String companyId, String departId, String signState, String nowTime) {
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT \n" +
                "v_sys_user_for_report.companyName,\n" +
                "v_sys_user_for_report.departName,\n" +
                "v_sys_user_for_report.roleName,\n" +
                "v_sys_user_for_report.userName,\n" +
                "CASE WHEN v_sys_user_for_report.frontFlag=1\n" +
                "THEN\n" +
                "v_sys_user_for_report.regionName\n" +
                "END AS regionName,\n" +
                "'" + dateTime + "' AS signDate,\n" +
                "sign.signState\n" +
                " FROM \n" +
                "\t(\n" +
                "\t\tSELECT  userId,COUNT(1) as signInCount,signState FROM v_sys_sign_leave \n" +
                "\t\tWHERE v_sys_sign_leave.signDate LIKE '" + dateTime + "%'  AND signDate <= '" + nowTime + "'\n");
        if (StringUtils.isNotBlank(signState)) {
            sb.append(" AND v_sys_sign_leave.signState=" + signState + "\n");
        } else {
            sb.append(" AND v_sys_sign_leave.signState is null");
        }
        sb.append("\t\tGROUP BY v_sys_sign_leave.userId\n" +
                "\t) as sign\n" +
                "\tLEFT JOIN v_sys_user_for_report ON sign.userId = v_sys_user_for_report.userId \n" +
                "\tLEFT JOIN(\n" +
                "\tSELECT FRONT_FLAG as frontFlag,COUNT(1) as shouldSignCount\n" +
                "\tFROM t_sys_should_holiday WHERE SHOULD_DATE LIKE '" + dateTime + "%' AND SHOULD_DATE <= '" + nowTime + "' AND HOLIDAY_FLAG = 0\n" +
                "\tGROUP BY FRONT_FLAG\n" +
                "\t) as shouldSign ON v_sys_user_for_report.frontFlag = shouldSign.frontFlag\n");
        if ("0".equals(signState)) {
            sb.append("\tWHERE sign.signInCount >= shouldSign.shouldSignCount\n");
        } else {
            sb.append("\tWHERE sign.signInCount <= shouldSign.shouldSignCount\n");
        }
        if (StringUtils.isNotBlank(companyId)) {
            sb.append(" AND v_sys_user_for_report.companyId='" + companyId + "'\n");

        } else {
            sb.append(" AND v_sys_user_for_report.departId='" + departId + "'\n");
        }
        sb.append(" AND v_sys_user_for_report.reportFlag=0 ");
        sb.append(" AND (v_sys_user_for_report.entryDate<='" + dateTime + "' " +
                "OR v_sys_user_for_report.entryDate LIKE '" + dateTime + "%')");
        List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
        return jsonList;

    }

    public List<JSONObject> getForSignInByTypeAndMonthTime(String dateTime, String companyId, String departId, String signState) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT v_sys_sign_leave.roleName,v_sys_sign_leave.companyName,t_sys_user.`NAME` AS userName,\n" +
                "v_sys_sign_leave.signDate,\n" +
                "v_sys_sign_leave.signState,\n" +
                "v_sys_sign_leave.startAddress\n" +
                "FROM v_sys_sign_leave \n" +
                "LEFT JOIN t_sys_user ON v_sys_sign_leave.userId=t_sys_user.ID\n");
        if (StringUtils.isNotBlank(companyId)) {
            sb.append(
                    "WHERE companyId='" + companyId + "'");
        } else {
            sb.append(" WHERE departId='" + departId + "'");
        }
        sb.append(" AND (t_sys_user.ENTRY_DATE <='" + dateTime + "' " +
                "OR t_sys_user.ENTRY_DATE LIKE '" + dateTime + "%') " +
                " AND signState=" + signState + " AND signDate LIKE '" + dateTime + "%'\n" +
                "ORDER BY t_sys_user.`NAME`,signDate");
        List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
        return jsonList;

    }

    public List<JSONObject> getForManagerInByTypeAndMonthTime(String dateTime, String userId, String signState) {
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT\n" +
                "\t*\n" +
                "FROM\n" +
                "\t`v_sys_sign_leave`\n" +
                "WHERE\n" +
                "1=1\n" +
                "AND userId='" + userId + "'\n" +
                "AND signDate like '" + dateTime + "%'\n");
        if (StringUtils.isNotBlank(signState)) {
            sb.append(
                    "AND signState= " + signState + "\n" +
                            ";");
        } else {
            sb.append(" AND signState IS NULL");
        }
        List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
        return jsonList;

    }

    public JSONObject getSignInByRegionUserId(String dateTime, String userId, String departId, String nowTime) {
        int len = dateTime.length();
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT COUNT(1) AS shouldCount,\n" +
                "COUNT(case when sysSign.signState=0 then 1 end) AS signInCount,\n" +
                "COUNT(case when sysSign.signState=1 then 1 end) AS leaveCount\n" +
                " FROM \n" +
                " (\n" +
                "SELECT userId,departName,roleName,userName,regionName,'" + dateTime + "' as signDate\n" +
                "FROM v_sys_user_for_report\n" +
                "WHERE 1 = 1 and entryDate<='" + dateTime + "'\n");
        if (len == 10) {
            sb.append("AND (jobState <> 1 OR (jobState = 1 AND leaveDate >'" + dateTime + "'))\n");
        } else {
            sb.append("AND (jobState <> 1 OR (jobState = 1 AND leaveDate >='" + dateTime + "'))\n");
        }
        if (StringUtils.isNotBlank(userId)) {
            sb.append(
                    "AND (\n" +
                            "  userId = '" + userId + "' \n" +
                            "\tOR parentId = '" + userId + "'\n" +
                            "  OR (\n" +
                            "\t\t\t\t\tuserId in(\n" +
                            "\t\t\t\t\t\t\t\t\t\tSELECT USER_ID FROM t_sys_user_2_region \n" +
                            "\t\t\t\t\t\t\t\t\t\tWHERE \n" +
                            "\t\t\t\t\t\t\t\t\t\tREGION_ID in(\n" +
                            "\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tSELECT REGION_ID FROM t_sys_user_2_region WHERE USER_ID = '" + userId + "'\n" +
                            "\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t)\n" +
                            "\t\t\t\t\t\t\t\t\t)\n" +
                            "\t\t\t\t\tAND v_sys_user_for_report.frontFlag = 1 \n" +
                            "\t\t\t\t)\n" +
                            "OR (departId in (\n" +
                            "\t\t\t\t\t\t\t\tselect t_sys_depart.ID from t_sys_depart \n" +
                            "\t\t\t\t\t\t\t\t\tLEFT JOIN t_sys_role ON t_sys_depart.ID=t_sys_role.DEPART_ID\n" +
                            "\t\t\t\t\t\t\t\t\tLEFT JOIN t_sys_user ON t_sys_role.ID =t_sys_user.ROLE_ID\n" +
                            "\t\t\t\t\t\t\t\t\tWHERE t_sys_user.ID='" + userId + "'))\n" +
                            ")\n");
        }
        if (StringUtils.isNotBlank(departId)) {
            sb.append(" AND v_sys_user_for_report.departId='" + departId + "'");
        }
        sb.append("AND v_sys_user_for_report.reportFlag=0\n" +
                "GROUP BY userId,departName,roleName,userName,regionName \n" +
                "ORDER BY companySortNo,departSortNo,roleSortNo,userSortNo) as sysUser\n" +
                "LEFT JOIN\n" +
                "(\n" +
                "select userId,signDate,SUBSTRING(signCreateTime,11,6) as signCreateTime,startAddress,SUBSTRING(signOutTime,11,6 ) as signOutTime,endAddress,signUrl,signOutUrl,signState\n" +
                " FROM\n" +
                " v_sys_sign_leave\n" +
                "\n");
        if (len == 10) {
            sb.append(" GROUP BY userId,LEFT(signDate,10)\n");
        } else {
            sb.append(" GROUP BY userId,LEFT(signDate,7)\n");
        }
        if (len == 10) {
            sb.append(" ) as sysSign ON sysUser.userId = sysSign.userId and sysUser.signDate = left(sysSign.signDate,10)\n");
        } else {
            sb.append(" ) as sysSign ON sysUser.userId = sysSign.userId and sysUser.signDate = left(sysSign.signDate,7)\n");
        }
        return super.queryForJsonObject(sb.toString());
    }

    public List<JSONObject> getSignExcel(String dateTime, String companyId) {
        StringBuffer sb = new StringBuffer();
        String afterMonth = DateUtils.getAfterMonth(dateTime);
        sb.append("SELECT\n" +
                "\tcompanyName,\n" +
                "\tuserId,\n" +
                "\tdepartName,\n" +
                "\tuserName,\n" +
                "\tjobState,\n" +
                "\tentryDate,\n" +
                "\tleaveDate,\n" +
                "\tfrontFlag,\n" +
                "\tmain,\n" +
                "IFNULL((SELECT SHOULD_SIGN_IN FROM t_sys_should_holiday WHERE SHOULD_DATE like '" + dateTime + "%'  AND FRONT_FLAG =v_sys_user_for_excel.frontFlag GROUP BY\tSHOULD_SIGN_IN),0) AS shouldCount\n" +
                "FROM\n" +
                "\tv_sys_user_for_excel\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND (\n" +
                "\tentryDate <= '" + dateTime + "'\n" +
                "\tOR entryDate LIKE '" + dateTime + "%'\n" +
                ")\n" +
                "AND (\n" +
                "\tjobState <> 1\n" +
                "\tOR (\n" +
                "\t\tjobState = 1\n" +
                "\t\tAND leaveDate >= '" + dateTime + "'\n" +
                "\t\tAND leaveDate <= '" + afterMonth + "'\n" +
                "\t)\n" +
                ")\n" +
                "AND reportFlag = 0\n" +
                "AND companyId = '" + companyId + "'\n" +
                "ORDER BY\n" +
                "\tcompanySortNo,\n" +
                "\tdepartSortNo,\n" +
                "\troleSortNo,\n" +
                "\tuserSortNo,\n" +
                "\tuserId,\n" +
                "\tsortNo");
        return super.queryForJsonList(sb.toString());
    }

    public List<JSONObject> getWrtReportExcel(String userId, String dateTime) {
        StringBuffer sb = new StringBuffer();
        sb.append("select SUBSTRING(REPORT_DATE,9,6) AS reportDate from t_wrt_main \n" +
                "WHERE USER_ID='" + userId + "' \n" +
                "AND REPORT_DATE like '" + dateTime + "%'\n" +
                "ORDER BY REPORT_DATE");
        return super.queryForJsonList(sb.toString());
    }

    public List<JSONObject> getLeaveAndAbsenceExcel(String userId, String dateTime) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT SUBSTRING(SIGN_DATE,9,6) AS signDate from t_sys_sign_in WHERE USER_ID='" + userId + "' \n" +
                "AND SIGN_DATE LIKE '" + dateTime + "%' ORDER BY signDate\n");
        return super.queryForJsonList(sb.toString());
    }

    public List<JSONObject> getBackGround(String dateTime) {
        StringBuffer sb = new StringBuffer();
        sb.append("select SUBSTRING(SHOULD_DATE,9,6) AS shouldDate FROM t_sys_should_holiday WHERE HOLIDAY_FLAG=1 AND FRONT_FLAG=0  AND SHOULD_DATE LIKE '" + dateTime + "%'\n" +
                "ORDER BY SHOULD_DATE\n\n");
        return super.queryForJsonList(sb.toString());
    }


    //本月实休=截止到今天为止未签到的天数
    public Integer getRealRest(String dateTime, String userId, String nowTime) {
        StringBuffer sb = new StringBuffer();
        sb.append("select COUNT(1) AS realRest from t_sys_sign_in WHERE SIGN_DATE like '" + dateTime + "%' " +
                " AND  SIGN_DATE <='" + nowTime + "' AND USER_ID='" + userId + "'\n");
        String count = super.queryForString(sb.toString());
        return Integer.parseInt(count);
    }

    //请假=工作日&&请假的天数
    public Integer getLeaveRest(String dateTime, String userId, String frontFlag) {
        StringBuffer sb = new StringBuffer();
        sb.append("select COUNT(1) AS leaveIn from t_sys_leave\n" +
                " WHERE LEAVE_DATE LIKE '" + dateTime + "%' AND USER_ID='" + userId + "'\n" +
                "AND LEAVE_DATE in(SELECT SHOULD_DATE from t_sys_should_holiday\n" +
                "WHERE FRONT_FLAG=" + frontFlag + " AND HOLIDAY_FLAG=0 AND SHOULD_DATE like '" + dateTime + "%')\n");
        String count = super.queryForString(sb.toString());
        return Integer.parseInt(count);
    }

    //出勤=工作日&&签到的天数
    public Integer getSignRest(String dateTime, String userId, String frontFlag) {
        StringBuffer sb = new StringBuffer();
        sb.append("select COUNT(1) AS signCount from t_sys_sign_in\n" +
                " WHERE SIGN_DATE LIKE '" + dateTime + "%' AND USER_ID='" + userId + "'\n" +
                "AND SIGN_DATE in(SELECT SHOULD_DATE from t_sys_should_holiday\n" +
                "WHERE FRONT_FLAG=" + frontFlag + " AND HOLIDAY_FLAG=0 AND SHOULD_DATE like '" + dateTime + "%')\n");
        String count = super.queryForString(sb.toString());
        return Integer.parseInt(count);
    }

    //加班=休息日&&签到的天数
    public Integer getOverTime(String dateTime, String userId, String frontFlag) {
        StringBuffer sb = new StringBuffer();
        sb.append("select COUNT(1) AS signCount from t_sys_sign_in\n" +
                " WHERE SIGN_DATE LIKE '" + dateTime + "%' AND USER_ID='" + userId + "'\n" +
                "AND SIGN_DATE in(SELECT SHOULD_DATE from t_sys_should_holiday\n" +
                "WHERE FRONT_FLAG=" + frontFlag + " AND HOLIDAY_FLAG=1 AND SHOULD_DATE like '" + dateTime + "%')\n");
        String count = super.queryForString(sb.toString());
        return Integer.parseInt(count);
    }
}
